package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import bd.DbAdapter;
import bd.DbAdapterMySql;
import bd.DbConfig;
import bean.QuestionBean;

public class DaoQuestionDB implements Dao {

	DbAdapter adapter = new DbAdapterMySql();
	
	public void updateQuestion(QuestionBean bean){
	
	}

	public boolean checkAdmin(String name, String pass) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		
		DbAdapter localAdapter = new DbAdapterMySql();
		String sql = null;

		sql = "SELECT `Pass` FROM `Users` WHERE `Nick`='"+name+"'";
		System.out.println(sql);
		try {
			con = localAdapter.getConnection();

			st = con.createStatement();
			rs = st.executeQuery(sql);

			rs.next();

			System.out.println(rs.getString(1));

			if (pass.equals(rs.getString(1)))
				return true;
			else
				return false;

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return false;
	}
	
	public void removeQuestion(QuestionBean bean){
		Connection con = null;
		Statement st = null;


		

		String sql = "DELETE  FROM `intrebare` where id=" + bean.getId();
System.out.println(sql);
		try {
			con = adapter.getConnection();
			st = con.createStatement();
			st.executeUpdate(sql);

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			try {
				st.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
	}

	public void InsertQuestion(QuestionBean bean) {
		Connection con = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		String id = null;
		DbConfig conf = DbConfig.getInstance();
		String[] params = new String[] { "localhost", "1521", "root", "",
				"lab1" };
		conf.setParams(params);

		try {
			con = adapter.getConnection();
			String sql = "INSERT INTO `lab1`.`raspuns` (`id`, `var1`, `var2`, `var3`, `var4`) VALUES (null, ?, ?, ?, ?)";
			stmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

			stmt.setString(1, bean.getResponse1());
			stmt.setString(2, bean.getResponse2());
			stmt.setString(3, bean.getResponse3());
			stmt.setString(4, bean.getResponse4());
			stmt.executeUpdate();

			rs = stmt.getGeneratedKeys();
			rs.next();
			id = rs.getString(1);
			con.close();
			System.out.println(id);
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			try {
				con.close();
				rs.close();
				stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		insertQuestionId(id, bean);
	}

	private void insertQuestionId(String id, QuestionBean bean) {
		Connection con = null;
		PreparedStatement stmt = null;
		DbAdapter localAdapter = new DbAdapterMySql();
		try {
			con = localAdapter.getConnection();
			String sql = "INSERT INTO `lab1`.`intrebare` (`id`, `question`, `idRaspuns`, `corectAnswer`) VALUES (null, ?, ?, ?)";
			stmt = con.prepareStatement(sql);

			System.out.println();

			stmt.setString(1, bean.getQuestion());
			stmt.setInt(2, Integer.parseInt(id));
			stmt.setString(3, bean.getTrueResponse());
			stmt.executeUpdate();
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			try {
				con.close();
				stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public boolean checkAnswer(String answer, int id)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {

		Connection con = null;
		Statement st = null;
		ResultSet rs = null;

		con = adapter.getConnection();

		String sql = "select corectAnswer FROM `intrebare` where id=" + id;

		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);

			rs.next();

			System.out.println(rs.getString(1));

			if (answer.equals(rs.getString(1)))
				return true;
			else
				return false;

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			st.close();
			rs.close();
			con.close();
		}

		return false;

	}

	public ArrayList<Integer> getAllId() {

		Connection con = null;
		Statement st = null;
		ResultSet rs = null;

		ArrayList<Integer> arrInt = new ArrayList<Integer>();

		try {
			con = adapter.getConnection();
		} catch (InstantiationException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		String sql = "select id FROM `intrebare`";

		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				arrInt.add(rs.getInt(1));
			}

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return arrInt;

	}

	public QuestionBean getQuestionBeanById(int id) {

		QuestionBean question = new QuestionBean();

		try {
			getQuestion(question, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return question;

	}

	private void getQuestion(QuestionBean question, int id)
			throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {

		Connection con = null;
		Statement st = null;
		ResultSet rs = null;

		con = adapter.getConnection();

		String sql = "SELECT * FROM `intrebare` where id = " + id;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			rs.next();
			System.out.println(sql);
			question.setId(rs.getInt(1));
			question.setQuestion(rs.getString(2));
			question.setTrueResponse(rs.getString(4));

			setResponse(question, rs.getInt(3));

			System.out.println(question.getTrueResponse());

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			st.close();
			rs.close();
			con.close();
		}
	}

	private void setResponse(QuestionBean question, int id)
			throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {

		Connection con = null;
		Statement st = null;
		ResultSet rs = null;

		String sql = "SELECT * FROM `raspuns` where id = " + id;

		con = adapter.getConnection();

		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			rs.next();
			System.out.println(sql);
			question.setResponse1(rs.getString(2));
			question.setResponse2(rs.getString(3));
			question.setResponse3(rs.getString(4));
			question.setResponse4(rs.getString(5));

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			st.close();
			rs.close();
			con.close();
		}

	}
}